// Licensed to the .NET Foundation under one or more agreements.
// The .NET Foundation licenses this file to you under the MIT license.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Diagnostics.CodeAnalysis;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.Data.Sqlite.Properties;
using Microsoft.Data.Sqlite.Utilities;
using SQLitePCL;
using static SQLitePCL.raw;

namespace Microsoft.Data.Sqlite;

/// <summary>
///     Represents a SQL statement to be executed against a SQLite database.
/// </summary>
/// <seealso href="https://docs.microsoft.com/dotnet/standard/data/sqlite/batching">Batching</seealso>
/// <seealso href="https://docs.microsoft.com/dotnet/standard/data/sqlite/database-errors">Database Errors</seealso>
/// <seealso href="https://docs.microsoft.com/dotnet/standard/data/sqlite/async">Async Limitations</seealso>
public class SqliteCommand : DbCommand
{
    private SqliteParameterCollection? _parameters;

    private readonly List<(sqlite3_stmt Statement, int ParamCount)> _preparedStatements = new(1);
    private SqliteConnection? _connection;
    private string _commandText = string.Empty;
    private bool _prepared;
    private int? _commandTimeout;

    /// <summary>
    ///     Initializes a new instance of the <see cref="SqliteCommand" /> class.
    /// </summary>
    public SqliteCommand()
    {
    }

    /// <summary>
    ///     Initializes a new instance of the <see cref="SqliteCommand" /> class.
    /// </summary>
    /// <param name="commandText">The SQL to execute against the database.</param>
    public SqliteCommand(string? commandText)
        => CommandText = commandText;

    /// <summary>
    ///     Initializes a new instance of the <see cref="SqliteCommand" /> class.
    /// </summary>
    /// <param name="commandText">The SQL to execute against the database.</param>
    /// <param name="connection">The connection used by the command.</param>
    public SqliteCommand(string? commandText, SqliteConnection? connection)
        : this(commandText)
        => Connection = connection;

    /// <summary>
    ///     Initializes a new instance of the <see cref="SqliteCommand" /> class.
    /// </summary>
    /// <param name="commandText">The SQL to execute against the database.</param>
    /// <param name="connection">The connection used by the command.</param>
    /// <param name="transaction">The transaction within which the command executes.</param>
    public SqliteCommand(string? commandText, SqliteConnection? connection, SqliteTransaction? transaction)
        : this(commandText, connection)
        => Transaction = transaction;

    /// <summary>
    ///     Gets or sets a value indicating how <see cref="CommandText" /> is interpreted. Only
    ///     <see cref="System.Data.CommandType.Text" /> is supported.
    /// </summary>
    /// <value>A value indicating how <see cref="CommandText" /> is interpreted.</value>
    public override CommandType CommandType
    {
        get => CommandType.Text;
        set
        {
            if (value != CommandType.Text)
            {
                throw new ArgumentException(Resources.InvalidCommandType(value));
            }
        }
    }

    /// <summary>
    ///     Gets or sets the SQL to execute against the database.
    /// </summary>
    /// <value>The SQL to execute against the database.</value>
    /// <seealso href="https://docs.microsoft.com/dotnet/standard/data/sqlite/batching">Batching</seealso>
    [AllowNull]
    public override string CommandText
    {
        get => _commandText;
        set
        {
            if (DataReader != null)
            {
                throw new InvalidOperationException(Resources.SetRequiresNoOpenReader(nameof(CommandText)));
            }

            if (value != _commandText)
            {
                DisposePreparedStatements();
                _commandText = value ?? string.Empty;
            }
        }
    }

    /// <summary>
    ///     Gets or sets the connection used by the command.
    /// </summary>
    /// <value>The connection used by the command.</value>
    public new virtual SqliteConnection? Connection
    {
        get => _connection;
        set
        {
            if (DataReader != null)
            {
                throw new InvalidOperationException(Resources.SetRequiresNoOpenReader(nameof(Connection)));
            }

            if (value != _connection)
            {
                DisposePreparedStatements();

                _connection?.RemoveCommand(this);
                _connection = value;
                value?.AddCommand(this);
            }
        }
    }

    /// <summary>
    ///     Gets or sets the connection used by the command. Must be a <see cref="SqliteConnection" />.
    /// </summary>
    /// <value>The connection used by the command.</value>
    protected override DbConnection? DbConnection
    {
        get => Connection;
        set => Connection = (SqliteConnection?)value;
    }

    /// <summary>
    ///     Gets or sets the transaction within which the command executes.
    /// </summary>
    /// <value>The transaction within which the command executes.</value>
    public new virtual SqliteTransaction? Transaction { get; set; }

    /// <summary>
    ///     Gets or sets the transaction within which the command executes. Must be a <see cref="SqliteTransaction" />.
    /// </summary>
    /// <value>The transaction within which the command executes.</value>
    protected override DbTransaction? DbTransaction
    {
        get => Transaction;
        set => Transaction = (SqliteTransaction?)value;
    }

    /// <summary>
    ///     Gets the collection of parameters used by the command.
    /// </summary>
    /// <value>The collection of parameters used by the command.</value>
    /// <seealso href="https://docs.microsoft.com/dotnet/standard/data/sqlite/parameters">Parameters</seealso>
    public new virtual SqliteParameterCollection Parameters
        => _parameters ??= [];

    /// <summary>
    ///     Gets the collection of parameters used by the command.
    /// </summary>
    /// <value>The collection of parameters used by the command.</value>
    protected override DbParameterCollection DbParameterCollection
        => Parameters;

    /// <summary>
    ///     Gets or sets the number of seconds to wait before terminating the attempt to execute the command.
    ///     Defaults to 30. A value of 0 means no timeout.
    /// </summary>
    /// <value>The number of seconds to wait before terminating the attempt to execute the command.</value>
    /// <remarks>
    ///     The timeout is used when the command is waiting to obtain a lock on the table.
    /// </remarks>
    /// <seealso href="https://docs.microsoft.com/dotnet/standard/data/sqlite/database-errors">Database Errors</seealso>
    public override int CommandTimeout
    {
        get => _commandTimeout ?? _connection?.DefaultTimeout ?? 30;
        set => _commandTimeout = value;
    }

    /// <summary>
    ///     Gets or sets a value indicating whether the command should be visible in an interface control.
    /// </summary>
    /// <value>A value indicating whether the command should be visible in an interface control.</value>
    public override bool DesignTimeVisible { get; set; }

    /// <summary>
    ///     Gets or sets a value indicating how the results are applied to the row being updated.
    /// </summary>
    /// <value>A value indicating how the results are applied to the row being updated.</value>
    public override UpdateRowSource UpdatedRowSource { get; set; }

    /// <summary>
    ///     Gets or sets the data reader currently being used by the command, or null if none.
    /// </summary>
    /// <value>The data reader currently being used by the command.</value>
    protected internal virtual SqliteDataReader? DataReader { get; set; }

    /// <summary>
    ///     Releases any resources used by the connection and closes it.
    /// </summary>
    /// <param name="disposing">
    ///     <see langword="true" /> to release managed and unmanaged resources;
    ///     <see langword="false" /> to release only unmanaged resources.
    /// </param>
    protected override void Dispose(bool disposing)
    {
        DisposePreparedStatements(disposing);

        if (disposing)
        {
            _connection?.RemoveCommand(this);
        }

        base.Dispose(disposing);
    }

    /// <summary>
    ///     Creates a new parameter.
    /// </summary>
    /// <returns>The new parameter.</returns>
    public new virtual SqliteParameter CreateParameter()
        => new();

    /// <summary>
    ///     Creates a new parameter.
    /// </summary>
    /// <returns>The new parameter.</returns>
    protected override DbParameter CreateDbParameter()
        => CreateParameter();

    /// <summary>
    ///     Creates a prepared version of the command on the database.
    /// </summary>
    public override void Prepare()
    {
        if (_connection?.State != ConnectionState.Open)
        {
            throw new InvalidOperationException(Resources.CallRequiresOpenConnection(nameof(Prepare)));
        }

        if (_prepared)
        {
            return;
        }

        using var enumerator = PrepareAndEnumerateStatements().GetEnumerator();
        while (enumerator.MoveNext())
        {
        }
    }

    /// <summary>
    ///     Executes the <see cref="CommandText" /> against the database and returns a data reader.
    /// </summary>
    /// <returns>The data reader.</returns>
    /// <exception cref="SqliteException">A SQLite error occurs during execution.</exception>
    /// <seealso href="https://docs.microsoft.com/dotnet/standard/data/sqlite/database-errors">Database Errors</seealso>
    /// <seealso href="https://docs.microsoft.com/dotnet/standard/data/sqlite/batching">Batching</seealso>
    public new virtual SqliteDataReader ExecuteReader()
        => ExecuteReader(CommandBehavior.Default);

    /// <summary>
    ///     Executes the <see cref="CommandText" /> against the database and returns a data reader.
    /// </summary>
    /// <param name="behavior">A description of the results of the query and its effect on the database.</param>
    /// <returns>The data reader.</returns>
    /// <exception cref="SqliteException">A SQLite error occurs during execution.</exception>
    /// <seealso href="https://docs.microsoft.com/dotnet/standard/data/sqlite/database-errors">Database Errors</seealso>
    /// <seealso href="https://docs.microsoft.com/dotnet/standard/data/sqlite/batching">Batching</seealso>
    public new virtual SqliteDataReader ExecuteReader(CommandBehavior behavior)
    {
        if (DataReader != null)
        {
            throw new InvalidOperationException(Resources.DataReaderOpen);
        }

        if (_connection?.State != ConnectionState.Open)
        {
            throw new InvalidOperationException(Resources.CallRequiresOpenConnection(nameof(ExecuteReader)));
        }

        if (Transaction != _connection.Transaction)
        {
            throw new InvalidOperationException(
                Transaction == null
                    ? Resources.TransactionRequired
                    : Resources.TransactionConnectionMismatch);
        }

        if (_connection.Transaction?.ExternalRollback == true)
        {
            throw new InvalidOperationException(Resources.TransactionCompleted);
        }

        var closeConnection = behavior.HasFlag(CommandBehavior.CloseConnection);

        var dataReader = new SqliteDataReader(this, GetStatements(), closeConnection);
        dataReader.NextResult();

        return DataReader = dataReader;
    }

    private IEnumerable<sqlite3_stmt> GetStatements()
    {
        foreach (var (stmt, expectedParams) in !_prepared
                     ? PrepareAndEnumerateStatements()
                     : _preparedStatements)
        {
            var boundParams = _parameters?.Bind(stmt, Connection!.Handle!) ?? 0;

            if (expectedParams != boundParams)
            {
                var unboundParams = new List<string>();
                for (var i = 1; i <= expectedParams; i++)
                {
                    var name = sqlite3_bind_parameter_name(stmt, i).utf8_to_string();

                    if (_parameters != null
                        && !_parameters.Cast<SqliteParameter>().Any(p => p.ParameterName == name))
                    {
                        unboundParams.Add(name);
                    }
                }

                if (sqlite3_libversion_number() < 3028000 || sqlite3_stmt_isexplain(stmt) == 0)
                {
                    throw new InvalidOperationException(Resources.MissingParameters(string.Join(", ", unboundParams)));
                }
            }

            yield return stmt;
        }
    }

    /// <summary>
    ///     Executes the <see cref="CommandText" /> against the database and returns a data reader.
    /// </summary>
    /// <param name="behavior">A description of query's results and its effect on the database.</param>
    /// <returns>The data reader.</returns>
    protected override DbDataReader ExecuteDbDataReader(CommandBehavior behavior)
        => ExecuteReader(behavior);

    /// <summary>
    ///     Executes the <see cref="CommandText" /> asynchronously against the database and returns a data reader.
    /// </summary>
    /// <returns>A task representing the asynchronous operation.</returns>
    /// <seealso href="https://docs.microsoft.com/dotnet/standard/data/sqlite/async">Async Limitations</seealso>
    /// <seealso href="https://docs.microsoft.com/dotnet/standard/data/sqlite/batching">Batching</seealso>
    /// <seealso href="https://docs.microsoft.com/dotnet/standard/data/sqlite/database-errors">Database Errors</seealso>
    public new virtual Task<SqliteDataReader> ExecuteReaderAsync()
        => ExecuteReaderAsync(CommandBehavior.Default, CancellationToken.None);

    /// <summary>
    ///     Executes the <see cref="CommandText" /> asynchronously against the database and returns a data reader.
    /// </summary>
    /// <param name="cancellationToken">The token to monitor for cancellation requests.</param>
    /// <returns>A task representing the asynchronous operation.</returns>
    /// <seealso href="https://docs.microsoft.com/dotnet/standard/data/sqlite/async">Async Limitations</seealso>
    /// <seealso href="https://docs.microsoft.com/dotnet/standard/data/sqlite/batching">Batching</seealso>
    /// <seealso href="https://docs.microsoft.com/dotnet/standard/data/sqlite/database-errors">Database Errors</seealso>
    /// <exception cref="OperationCanceledException">If the <see cref="CancellationToken" /> is canceled.</exception>
    public new virtual Task<SqliteDataReader> ExecuteReaderAsync(CancellationToken cancellationToken)
        => ExecuteReaderAsync(CommandBehavior.Default, cancellationToken);

    /// <summary>
    ///     Executes the <see cref="CommandText" /> asynchronously against the database and returns a data reader.
    /// </summary>
    /// <param name="behavior">A description of query's results and its effect on the database.</param>
    /// <returns>A task representing the asynchronous operation.</returns>
    /// <seealso href="https://docs.microsoft.com/dotnet/standard/data/sqlite/async">Async Limitations</seealso>
    /// <seealso href="https://docs.microsoft.com/dotnet/standard/data/sqlite/batching">Batching</seealso>
    /// <seealso href="https://docs.microsoft.com/dotnet/standard/data/sqlite/database-errors">Database Errors</seealso>
    public new virtual Task<SqliteDataReader> ExecuteReaderAsync(CommandBehavior behavior)
        => ExecuteReaderAsync(behavior, CancellationToken.None);

    /// <summary>
    ///     Executes the <see cref="CommandText" /> asynchronously against the database and returns a data reader.
    /// </summary>
    /// <param name="behavior">A description of query's results and its effect on the database.</param>
    /// <param name="cancellationToken">The token to monitor for cancellation requests.</param>
    /// <returns>A task representing the asynchronous operation.</returns>
    /// <seealso href="https://docs.microsoft.com/dotnet/standard/data/sqlite/async">Async Limitations</seealso>
    /// <seealso href="https://docs.microsoft.com/dotnet/standard/data/sqlite/batching">Batching</seealso>
    /// <seealso href="https://docs.microsoft.com/dotnet/standard/data/sqlite/database-errors">Database Errors</seealso>
    /// <exception cref="OperationCanceledException">If the <see cref="CancellationToken" /> is canceled.</exception>
    public new virtual Task<SqliteDataReader> ExecuteReaderAsync(
        CommandBehavior behavior,
        CancellationToken cancellationToken)
    {
        cancellationToken.ThrowIfCancellationRequested();

        return Task.FromResult(ExecuteReader(behavior));
    }

    /// <summary>
    ///     Executes the <see cref="CommandText" /> asynchronously against the database and returns a data reader.
    /// </summary>
    /// <param name="behavior">A description of query's results and its effect on the database.</param>
    /// <param name="cancellationToken">The token to monitor for cancellation requests.</param>
    /// <returns>A task representing the asynchronous operation.</returns>
    /// <seealso href="https://docs.microsoft.com/dotnet/standard/data/sqlite/async">Async Limitations</seealso>
    /// <exception cref="OperationCanceledException">If the <see cref="CancellationToken" /> is canceled.</exception>
    protected override async Task<DbDataReader> ExecuteDbDataReaderAsync(
        CommandBehavior behavior,
        CancellationToken cancellationToken)
        => await ExecuteReaderAsync(behavior, cancellationToken).ConfigureAwait(false);

    /// <summary>
    ///     Executes the <see cref="CommandText" /> against the database.
    /// </summary>
    /// <returns>The number of rows inserted, updated, or deleted. -1 for SELECT statements.</returns>
    /// <exception cref="SqliteException">A SQLite error occurs during execution.</exception>
    /// <seealso href="https://docs.microsoft.com/dotnet/standard/data/sqlite/database-errors">Database Errors</seealso>
    public override int ExecuteNonQuery()
    {
        if (_connection?.State != ConnectionState.Open)
        {
            throw new InvalidOperationException(Resources.CallRequiresOpenConnection(nameof(ExecuteNonQuery)));
        }

        var reader = ExecuteReader();
        reader.Dispose();

        return reader.RecordsAffected;
    }

    /// <summary>
    ///     Executes the <see cref="CommandText" /> against the database and returns the result.
    /// </summary>
    /// <returns>The first column of the first row of the results, or null if no results.</returns>
    /// <exception cref="SqliteException">A SQLite error occurs during execution.</exception>
    /// <seealso href="https://docs.microsoft.com/dotnet/standard/data/sqlite/database-errors">Database Errors</seealso>
    public override object? ExecuteScalar()
    {
        if (_connection?.State != ConnectionState.Open)
        {
            throw new InvalidOperationException(Resources.CallRequiresOpenConnection(nameof(ExecuteScalar)));
        }

        using var reader = ExecuteReader();
        return reader.Read()
            ? reader.GetValue(0)
            : null;
    }

    /// <summary>
    ///     Attempts to cancel the execution of the command. Does nothing.
    /// </summary>
    public override void Cancel()
    {
    }

    private IEnumerable<(sqlite3_stmt Statement, int ParamCount)> PrepareAndEnumerateStatements()
    {
        DisposePreparedStatements(disposing: false);

        var byteCount = Encoding.UTF8.GetByteCount(_commandText);
        var sql = new byte[byteCount + 1];
        Encoding.UTF8.GetBytes(_commandText, 0, _commandText.Length, sql, 0);

        var totalElapsedTime = TimeSpan.Zero;
        int rc;
        sqlite3_stmt stmt;
        var start = 0;
        do
        {
            var timer = SharedStopwatch.StartNew();

            ReadOnlySpan<byte> tail;
            while (IsBusy(rc = sqlite3_prepare_v2(_connection!.Handle, sql.AsSpan(start), out stmt, out tail)))
            {
                if (CommandTimeout != 0
                    && (totalElapsedTime + timer.Elapsed).TotalMilliseconds >= CommandTimeout * 1000L)
                {
                    break;
                }

                Thread.Sleep(150);
            }

            totalElapsedTime += timer.Elapsed;
            start = sql.Length - tail.Length;

            SqliteException.ThrowExceptionForRC(rc, _connection.Handle);

            // Statement was empty, white space, or a comment
            if (stmt.IsInvalid)
            {
                if (start < byteCount)
                {
                    continue;
                }

                break;
            }

            var paramsCount = sqlite3_bind_parameter_count(stmt);
            var statementWithParams = (stmt, paramsCount);

            _preparedStatements.Add(statementWithParams);

            yield return statementWithParams;
        }
        while (start < byteCount);

        _prepared = true;
    }

    private void DisposePreparedStatements(bool disposing = true)
    {
        if (disposing
            && DataReader != null)
        {
            DataReader.Dispose();
            DataReader = null;
        }

        if (_preparedStatements != null)
        {
            foreach (var (stmt, _) in _preparedStatements)
            {
                stmt.Dispose();
            }

            _preparedStatements.Clear();
        }

        _prepared = false;
    }

    private static bool IsBusy(int rc)
        => rc is SQLITE_LOCKED or SQLITE_BUSY or SQLITE_LOCKED_SHAREDCACHE;
}
